package com.gwtjs.icustom.utils;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import com.gwtjs.icustom.pojo.ServerHost;
import com.gwtjs.icustom.pojo.User;

@Component
public class ImportExcelUtil {
	
	private final static Logger logger = LoggerFactory.getLogger(ImportExcelUtil.class);
	
	/**
	 * # 上传用户列表
	 * @param fileName
	 * @param is
	 * @return
	 * @throws Exception
	 */
	public List<User> excelToUserObject(Workbook workbook) throws Exception{
        
        if (null==workbook){
            return null;
        }
        List<User> userList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            logger.info("SheetName : ",sheet.getSheetName());
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    User user = new User();
                    /*
                     row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    user.setId(Integer.parseInt(row.getCell(0).getStringCellValue())); // 获取表格单元格并给User设置值
                     */

                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    user.setUsername(row.getCell(0).getStringCellValue());
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    user.setAccount(row.getCell(1).getStringCellValue());
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    user.setPassword(row.getCell(2).getStringCellValue());
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    user.setAddress(row.getCell(3).getStringCellValue());

                    userList.add(user);
                }
            }
        }
        return userList;

    }
	
    /**
     * # 上传主机列表
     * @param fileName
     * @param is
     * @return
     * @throws Exception
     */
	public List<ServerHost> excelToServerHostObject(Workbook workbook) throws Exception{
        
        List<ServerHost> hostList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            logger.info("SheetName : ",sheet.getSheetName());
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    ServerHost host = new ServerHost();
					
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                    host.setHostIp(row.getCell(0).getStringCellValue());
                    if(null!=row.getCell(1)) {
                    	row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                        host.setHostName(row.getCell(1).getStringCellValue());
                    }
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    host.setHostPort(Integer.parseInt(row.getCell(2).getStringCellValue()));
                    
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                    host.setAccount(row.getCell(3).getStringCellValue());
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    host.setPassword(row.getCell(4).getStringCellValue());

                    hostList.add(host);
                }
            }
        }
        return hostList;

    }

    // 判断传入的文件是哪种类型的excel文件
    public Workbook getWorkbook(String fileName,InputStream is) throws Exception{
        Workbook workbook = null;
        String name = fileName.substring(fileName.lastIndexOf("."));

        if (".xls".equals(name)){
            workbook = new HSSFWorkbook(is);
        }
        if (".xlsx".equals(name)){
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }


}
